package br.com.registrofinancas.database;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import br.com.registrofinancas.model.CategoryVO.CategoryType;
import br.com.registrofinancas.model.ExpenseVO;
import br.com.registrofinancas.model.IncomeVO;
import br.com.registrofinancas.model.MovimentationVO;
import br.com.registrofinancas.model.MovimentoOrigemVO;
import br.com.registrofinancas.util.UsuarioUtils;
import android.content.Context;
import android.database.Cursor;
import android.util.Log;

public class MovimentosSaidaDAO extends MovimentosDAO{
	protected static final String INSERT = "INSERT INTO " + TABELA + "(id_categoria, valor, data, observacao, latitude, longitude) VALUES (?,?,?,?,?,?)";
	
	public MovimentosSaidaDAO(Context contexto) {
		super(contexto);
	}
	
	public long insert(ExpenseVO saida) {
		long retorno = 0;
		this.insertStatement = this.db.compileStatement(INSERT);		
		this.insertStatement.bindLong(1, saida.getCategory().getId());
		this.insertStatement.bindDouble(2, saida.getValue());
		this.insertStatement.bindString(3, FORMATA_DATA.format(saida.getOperationDate()));
		this.insertStatement.bindString(4, saida.getDescription());
		this.insertStatement.bindDouble(5, saida.getLatitude());
		this.insertStatement.bindDouble(6, saida.getLongitude());
		db.beginTransaction();
		try{
			retorno = insertStatement.executeInsert();
			if (retorno > 0){
				for (MovimentoOrigemVO origem : saida.getMovimentosOrigem()) {
					MovimentosEntradaDAO entradaDAO = new MovimentosEntradaDAO(contexto);
					IncomeVO entrada = entradaDAO.getEntradaPorId(origem.getEntrada().getId());
					entrada.setSaldo(entrada.getSaldo() - origem.getValor());
					entradaDAO.update(entrada);
					
					MovimentosOrigemDAO origemDAO = new MovimentosOrigemDAO(contexto);
					origem.getSaida().setId((int)retorno);
					if(origemDAO.insert(origem) == 0) {
						retorno = 0;
						break;
					}
				}
				if(retorno > 0){
					db.setTransactionSuccessful();
				}
			}
		}catch (Exception e) {
			Log.e("erro",e.getCause().toString());
		}finally{
			db.endTransaction();
		}
		return retorno;
	}

	public ExpenseVO getSaidaPorId( Integer id ) {
		ExpenseVO saida = null;
		Cursor cursor = 
				this.db.query( TABELA, new String[ ] { "id", "id_categoria", "valor", "data", "observacao", "latitude", "longitude" }, 
							   "id = ?", new String[]{id.toString()}, null, null, "id" ) ;
		
		if (cursor.moveToFirst( )) {
				
			try {
				
				saida = new ExpenseVO( ) ;
				saida.setId( cursor.getInt( 0 ) ) ;
				
				CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
				saida.setCategory( categoriaDAO.selectById(cursor.getInt( 1 )) ) ;
				
				saida.setValue( cursor.getDouble( 2 ) ) ;
				saida.setOperationDate( FORMATA_DATA.parse(cursor.getString( 3 )) ) ;
				saida.setDescription( cursor.getString( 4 ) ) ;
				saida.setLatitude(cursor.getDouble(5));
				saida.setLongitude(cursor.getDouble(6));
				MovimentosOrigemDAO movimentosOrigemDAO = new MovimentosOrigemDAO(contexto);
				saida.setMovimentosOrigem(movimentosOrigemDAO.getMovimentosOrigemPorIdSaida(saida.getId(),saida));
			} catch (ParseException e) {
				e.printStackTrace();
			}

		}
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		return saida ;
	}	
	
	public List<ExpenseVO> getSaidas( ) {
		List<ExpenseVO> list = new ArrayList<ExpenseVO>( ) ;

		StringBuilder query = new StringBuilder();
		query.append("SELECT DEB.id, DEB.id_categoria, DEB.valor,  DEB.data, DEB.observacao, DEB.latitude, DEB.longitude\n");
		query.append("FROM MOVIMENTOS DEB\n");
		query.append("INNER JOIN CATEGORIAS CAT ON CAT.id = CRE.id_categoria\n");
		query.append("WHERE CAT.tipo = ?\n");
		query.append("  AND CAT.id_usuario = ?\n");
		
		String[] parametros = {	String.valueOf(CategoryType.OUTCOME.ordinal()),
								UsuarioUtils.getIdUsuario().toString()};
		
		Cursor cursor = this.db.rawQuery(query.toString(), parametros);
		
		if (cursor.moveToFirst( )) {
			CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
			MovimentosOrigemDAO movimentosOrigemDAO = new MovimentosOrigemDAO(contexto);			
			do {
				try {
					ExpenseVO saida = new ExpenseVO( ) ;
					saida.setId( cursor.getInt( 0 ) ) ;
					saida.setCategory( categoriaDAO.selectById(cursor.getInt( 1 )) ) ;
					saida.setValue( cursor.getDouble( 2 ) ) ;
					saida.setOperationDate(FORMATA_DATA.parse(cursor.getString(3)));
					saida.setDescription( cursor.getString( 4 ) ) ;
					saida.setLatitude(cursor.getDouble(5));
					saida.setLongitude(cursor.getDouble(6));
					saida.setMovimentosOrigem(movimentosOrigemDAO.getMovimentosOrigemPorIdSaida(saida.getId(),saida));				
					list.add( saida ) ;
				} catch (ParseException e) {
					e.printStackTrace();
				}				
			}
			while (cursor.moveToNext( )) ;
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return list ;
	}
	
	public List<ExpenseVO> getSaidasPorMes( Date data ) {
		List<ExpenseVO> list = new ArrayList<ExpenseVO>( ) ;
		StringBuilder query = new StringBuilder();
		query.append("SELECT MOV.id, MOV.id_categoria, MOV.valor, MOV.data, MOV.observacao, MOV.latitude, MOV.longitude\n");
		query.append("FROM MOVIMENTOS MOV\n");
		query.append("JOIN CATEGORIAS CAT ON CAT.id = MOV.id_categoria\n");
		query.append("WHERE SUBSTR(data,1,7) = ?\n");
		query.append("  AND CAT.tipo = ?\n");		
		query.append("  AND CAT.id_usuario = ?\n");		
		query.append("ORDER BY MOV.id\n");
		
		String [] parametros =  {new SimpleDateFormat("yyyy-MM").format(data) , 
								 String.valueOf(CategoryType.OUTCOME.ordinal()),
								 UsuarioUtils.getIdUsuario().toString()};
		
		Cursor cursor = db.rawQuery(query.toString(), parametros) ;
		
		if (cursor.moveToFirst( )) {
			CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
			MovimentosOrigemDAO movimentosOrigemDAO = new MovimentosOrigemDAO(contexto);
			do {
				try {
					ExpenseVO saida = new ExpenseVO( ) ;
					saida.setId( cursor.getInt( 0 ) ) ;
					saida.setCategory( categoriaDAO.selectById(cursor.getInt( 1 )) ) ;
					saida.setValue( cursor.getDouble( 2 ) ) ;
					saida.setOperationDate( FORMATA_DATA.parse(cursor.getString( 3 )) ) ;
					saida.setDescription( cursor.getString( 4 ) ) ;
					saida.setLatitude(cursor.getDouble(5));
					saida.setLongitude(cursor.getDouble(6));					
					saida.setMovimentosOrigem(movimentosOrigemDAO.getMovimentosOrigemPorIdSaida(saida.getId(),saida));
					list.add( saida ) ;					
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			while (cursor.moveToNext( )) ;
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return list ;
	}
	
	public List<ExpenseVO> getSaidasPorDia( Date data ) {
		List<ExpenseVO> list = new ArrayList<ExpenseVO>( ) ;
		StringBuilder query = new StringBuilder();
		query.append("SELECT MOV.id, MOV.id_categoria, MOV.valor, MOV.data, MOV.observacao, MOV.latitude, MOV.longitude\n");
		query.append("FROM MOVIMENTOS MOV\n");
		query.append("JOIN CATEGORIAS CAT ON CAT.id = MOV.id_categoria\n");
		query.append("WHERE data = ?\n");
		query.append("  AND CAT.tipo = ?\n");		
		query.append("  AND CAT.id_usuario = ?\n");		
		query.append("ORDER BY MOV.id\n");
		
		String [] parametros =  {MovimentosDAO.FORMATA_DATA.format(data) , 
								 String.valueOf(CategoryType.OUTCOME.ordinal()),
								 UsuarioUtils.getIdUsuario().toString()};
		
		Cursor cursor = db.rawQuery(query.toString(), parametros) ;
		
		if (cursor.moveToFirst( )) {
			CategoriasDAO categoriaDAO = new CategoriasDAO(contexto);
			MovimentosOrigemDAO movimentosOrigemDAO = new MovimentosOrigemDAO(contexto);
			do {
				try {
					ExpenseVO saida = new ExpenseVO( ) ;
					saida.setId( cursor.getInt( 0 ) ) ;
					saida.setCategory( categoriaDAO.selectById(cursor.getInt( 1 )) ) ;
					saida.setValue( cursor.getDouble( 2 ) ) ;
					saida.setOperationDate( FORMATA_DATA.parse(cursor.getString( 3 )) ) ;
					saida.setDescription( cursor.getString( 4 ) ) ;
					saida.setLatitude(cursor.getDouble(5));
					saida.setLongitude(cursor.getDouble(6));					
					saida.setMovimentosOrigem(movimentosOrigemDAO.getMovimentosOrigemPorIdSaida(saida.getId(),saida));
					list.add( saida ) ;					
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			while (cursor.moveToNext( )) ;
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return list ;
	}
	
	public Double getValorTotalSaidasPorMes( Date data  ) {

		StringBuilder query = new StringBuilder();
		query.append("SELECT SUBSTR(data,1,7), SUM(DEB.valor) valor\n");
		query.append("FROM MOVIMENTOS DEB\n");
		query.append("INNER JOIN CATEGORIAS CAT ON CAT.id = DEB.id_categoria\n");
		query.append("WHERE CAT.tipo = ?\n");
		query.append("  AND CAT.id_usuario = ?\n");
		query.append("  AND  SUBSTR(data,1,7) = ?\n");
		query.append("GROUP BY SUBSTR(data,1,7)\n");
		
		String[] parametros = {	String.valueOf(CategoryType.OUTCOME.ordinal()),
								UsuarioUtils.getIdUsuario().toString(),
								new SimpleDateFormat("yyyy-MM").format(data)};
		
		Cursor cursor = this.db.rawQuery(query.toString(), parametros);
		Double valor = 0.0;
		
		if (cursor.moveToFirst( )) {
			valor = cursor.getDouble( 1 );
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return valor;
	}

	public Double getQuantidadeSaidasPorMes( Date data  ) {

		StringBuilder query = new StringBuilder();
		query.append("SELECT SUBSTR(data,1,7), COUNT(DEB.valor) valor\n");
		query.append("FROM MOVIMENTOS DEB\n");
		query.append("INNER JOIN CATEGORIAS CAT ON CAT.id = DEB.id_categoria\n");
		query.append("WHERE CAT.tipo = ?\n");
		query.append("  AND CAT.id_usuario = ?\n");
		query.append("  AND  SUBSTR(data,1,7) = ?\n");
		query.append("GROUP BY SUBSTR(data,1,7)\n");
		
		String[] parametros = {	String.valueOf(CategoryType.OUTCOME.ordinal()),
								UsuarioUtils.getIdUsuario().toString(),
								new SimpleDateFormat("yyyy-MM").format(data)};
		
		Cursor cursor = this.db.rawQuery(query.toString(), parametros);
		Double valor = 0.0;
		
		if (cursor.moveToFirst( )) {
			valor = cursor.getDouble( 1 );
		}
		
		if (null != cursor && !cursor.isClosed( )) {
			cursor.close( ) ;
		}
		
		return valor;
	}
	
	public long delete( ExpenseVO saida ){
		MovimentosOrigemDAO origem = new MovimentosOrigemDAO(contexto);
		origem.deletePorSaida(saida);
		return super.delete(saida);
	}
	
}